package connectDB;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import entities.Packetentities;

public class PacketDB extends AbstractDB implements IDB<Packetentities,Boolean,String> {

	@Override
	public Boolean add(Packetentities e) {
		String sql = "insert into packet values(?)";
		Connection con =this.condb.getConnectDB();
		PreparedStatement pst;

		try {
			pst=con.prepareStatement(sql);
			pst.setString(1, e.getPacketname());
			//pst.setInt(1, e.getPacketid());
			return pst.executeUpdate() > 0;
		} catch (SQLException e1) {
			// TODO Auto-generated catch block
			e1.printStackTrace();
		}
		return false;
	}

	@Override
	public Boolean delete(String k) {
		String sql = "delete from packet where packetid='"+k+"'";
		Connection con =this.condb.getConnectDB();
		try {
			Statement st = con.createStatement();
			return st.executeUpdate(sql) >0;
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return false;
	}

	@Override
	public Boolean update(Packetentities e) {
		String sql = "update packet set packetname=? where packetid=?";
		Connection con =this.condb.getConnectDB();
		PreparedStatement pst;

		try {
			pst=con.prepareStatement(sql);
			pst.setString(1, e.getPacketname());
			pst.setInt(2, e.getPacketid());
			return pst.executeUpdate() > 0;
		} catch (SQLException e1) {
			// TODO Auto-generated catch block
			e1.printStackTrace();
		}
		return false;
	}

	
	
	
	@Override
	public Packetentities find(String k) {
		String sql="select * from packet where packetid=?";
		Connection con = this.condb.getConnectDB();
		PreparedStatement pst;
		Packetentities ld = null;
		try {
			pst= con.prepareStatement(sql);
			pst.setString(1, k);
			ResultSet rs = pst.executeQuery();
			if(rs.next())
			{
				int idd=rs.getInt("packetid");
				String namee=rs.getString("packetname");
				ld= new Packetentities(idd, namee);			}
			rs.close();
			pst.close();
			con.close();
		} catch (SQLException e) {
		
			e.printStackTrace();
		}
	
		return ld;
	}

	@Override
	public List<Packetentities> findAll() {
		Connection con = this.condb.getConnectDB();
		String sql= "select * from packet";
		Packetentities pack=null;
		List<Packetentities> ls= new ArrayList();
		
		try {
			Statement st= con.createStatement();
			ResultSet rs= st.executeQuery(sql);
			while(rs.next()){
				int id=rs.getInt("packetid");
				String name=rs.getString("packetname");
				pack = new Packetentities(id, name);
				ls.add(pack);
			}
			return ls;		
		    
		} catch (SQLException e) {
		
			e.printStackTrace();
		}		
		return null;
	}
	


	@Override
	public Boolean delete1(int i) {
		// TODO Auto-generated method stub
		return null;
	}

	// Phan Trang ..............................................................
	
	
		public List<Packetentities> findvspaging (int start, int end){
			
			String sql = "select * from (select ROW_NUMBER() over (order by  packetid asc) as row"
					+", * from packet) AS LogWithRowNumbers "
					+"where row>="+start+" and row<="+end;
			
			Connection con = this.condb.getConnectDB();
			List<Packetentities> listld = new ArrayList<Packetentities>();
			try {
				Statement st = con.createStatement();
				ResultSet rs = st.executeQuery(sql);
				while(rs.next())
				{
					int id=rs.getInt("packetid");
					String name=rs.getString("packetname");
					Packetentities ld = new Packetentities(id ,name);
					listld.add(ld);
				}
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			
			return listld;
		}
		
		// lay tong so dong ..............
		public int findtotalrows()
		{
			int count=0;
			String sql="SELECT COUNT(packetid)as Totalrow FROM packet";
			Connection con = this.condb.getConnectDB();
			try {
				Statement st = con.createStatement();
				ResultSet rs = st.executeQuery(sql);
				if(rs.next())
				{
					count =Integer.parseInt(rs.getString(1));
					//count = rs.getInt(1);
				}
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			
			
			return count;
		}
public static void main(String[] args) {
	PacketDB x = new PacketDB();
	System.out.println(x.findtotalrows());


	for(Packetentities j : x.findvspaging(1, 5))
	{
		System.out.println(j.getPacketid());
		System.out.println(j.getPacketname());
	}
}

@Override
public Packetentities find1(int k) {
	// TODO Auto-generated method stub
	return null;
}
	
}
